﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Cocbook_DTO;
using Cocbook_DAL;
using System.Data;

namespace Cocbook_DAL
{
    public class OrderDAL
    {
        public bool CreateOrder(Order o)
        {
            /////////////INSERT INTO Staff VALUES(@name, @user, @pass, @position)
            DB.SetQuery("INSERT INTO [Order] VALUES(@cID,1, @date, @address, @sub, @ship, @tranfered, @type)");
            DB.AddParameters("cID", o.Customer.CustomerID);
            DB.AddParameters("date", o.Date);
            DB.AddParameters("address", o.Address);
            DB.AddParameters("sub", o.Total);
            DB.AddParameters("ship", o.ShipCost);
            DB.AddParameters("tranfered", o.Transfered);
            DB.AddParameters("type", o.CheckOutType);
            try
            {
                DB.ExeNoneQuery();
                return true;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Program.Main.Test.CreateOrder", e.ToString());
                return false;
            }
        }

        public bool UpdateOrder(Order o)
        {
            DB.SetQuery("UPDATE [Order] SET CustomerID = @cID, Date = @date, Address = @address, Total = @sub, Shipcost = @ship,"+
                        " Transfered = @tranfered, CheckOutType = @type WHERE OrderID = @id");
            DB.AddParameters("id", o.OrderID);
            DB.AddParameters("cID", o.Customer.CustomerID);
            DB.AddParameters("date", o.Date);
            DB.AddParameters("address", o.Address);
            DB.AddParameters("sub", o.Total);
            DB.AddParameters("ship", o.ShipCost);
            DB.AddParameters("tranfered", o.Transfered);
            DB.AddParameters("type", o.CheckOutType);
            try
            {
                DB.ExeNoneQuery();
                return true;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Program.Main.Test.UpdateOrder", e.ToString());
                return false;
            }
        }

        public bool DeleteOrder(int oID)
        {
            DB.SetQuery("DELETE FROM [Order] WHERE OrderID = @id");
            DB.AddParameters("id", oID);
            try
            {
                DB.ExeNoneQuery();
                return true;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Program.Main.Test.DeleteOrder", e.ToString());
                return false;
            }
        }

        public Int32 GetMaxOrderID()
        {
            try
            {
                DB.SetQuery("SELECT MAX(OrderID)FROM [Order]");
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                int max = 0;
                if (llo.Count > 0)
                {
                    max = Convert.ToInt16(llo[0][0]);
                }
                else "".Substring(2);
                return max;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Program.Main.Test.GetMaxOrderID", e.ToString());
                return 0;
            }
        }

        public Order GetOrderByID(int id)
        {
            try
            {
                DB.SetQuery("SELECT OrderID, CustomerID, Date, Address, Total, Shipcost, Transfered, CheckOutType FROM [Order] WHERE OrderID = @id");
                DB.AddParameters("id", id);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                Order o = new Order();
                if (llo.Count > 0)
                {
                    o.OrderID = Convert.ToInt16(llo[0][0]);
                    o.Customer = (new CustomerDAL()).GetCustomerById(Convert.ToInt16(llo[0][1]));
                    o.Date = Convert.ToDateTime(llo[0][2]);
                    o.Address = Convert.ToString(llo[0][3]);
                    o.Total = Convert.ToDouble(llo[0][4]);
                    o.ShipCost = Convert.ToDouble(llo[0][5]);
                    o.Transfered = Convert.ToBoolean(llo[0][6]);
                    o.CheckOutType = Convert.ToInt16(llo[0][7]);
                }
                else "".Substring(2);
                return o;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Program.Main.Test.GetOrderByID", e.ToString());
                return null;
            }
        }

        public List<Order> GetAllOrderByFilter(int oid, int cid, DateTime date, double ftotal, double ltotal)
        {
            try
            {
                DB.SetQuery("SELECT OrderID, ProjectID, CustomerID, StaffID, Date, Total, Paid, CheckOutType FROM [Order] WHERE "+
                            "((OrderID = @bid)or (@bid = 0)) and" +
                            "((CustomerID = @cid)or (@cid = 0)) and"+
                            "((convert(nvarchar(30),Date,103)= @date) or (@date = '1/1/1800')) and"+
                            "((Total >= @ftotal)or (@ftotal = 0)) and"+
                            "((Total <= @ltotal)or (@ltotal = 0));");
                DB.AddParameters("bid", oid);
                DB.AddParameters("cid", cid);
                DB.AddParameters("date", date);
                DB.AddParameters("ftotal", ftotal);
                DB.AddParameters("ltotal", ltotal);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                List<Order> order = new List<Order>();
                Order o = new Order();
                if (llo.Count > 0)
                {
                    foreach (List<object> lo in llo)
                    {
                        o = new Order();
                        o.OrderID = Convert.ToInt16(lo[0]);
                        o.Customer = (new CustomerDAL()).GetCustomerById(Convert.ToInt16(llo[0][1]));
                        o.Date = Convert.ToDateTime(lo[2]);
                        o.Address = Convert.ToString(lo[3]);
                        o.Total = Convert.ToDouble(lo[4]);
                        o.ShipCost = Convert.ToDouble(lo[5]);
                        o.Transfered = Convert.ToBoolean(lo[6]);
                        o.CheckOutType = Convert.ToInt16(lo[7]);
                        order.Add(o);
                    }
                }
                else
                {
                    "".Substring(2);
                }
                return order;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Program.Main.Test.GetAllOrder", e.ToString());
                return null;
            }
        }

        public DataTable GetAllOrderInProject(Project p)
        {
            try
            {
                DB.SetQuery(@"SELECT        dbo.Book.ISBN, dbo.Book.Title, dbo.Book.Issuer, dbo.Customer.CName
                                FROM            dbo.Order_Book INNER JOIN
                                                         dbo.[Order] ON dbo.Order_Book.OrderID = dbo.[Order].OrderID INNER JOIN
                                                         dbo.Book ON dbo.Order_Book.BookID = dbo.Book.BookID INNER JOIN
                                                         dbo.Customer ON dbo.[Order].CustomerID = dbo.Customer.CustomerID
                                WHERE        (dbo.[Order].Date >= @sDate) AND (dbo.[Order].Date =< @eDate)");
                DB.AddParameters("sDate", p.StartDate);
                DB.AddParameters("eDate", p.EndDate);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                DataTable result = new DataTable();
                result.Columns.Add("ISBN");
                result.Columns.Add("Tên sách");
                result.Columns.Add("Nhà phát hành");
                result.Columns.Add("Tên khách đặt mua");
                if (llo.Count > 0)
                {
                    foreach (List<object> lo in llo)
                    {
                        DataRow row = result.NewRow();
                        row[0] = Convert.ToInt64(lo[0]);
                        row[1] = Convert.ToString(lo[1]);
                        row[2] = Convert.ToString(lo[2]);
                        row[3] = Convert.ToString(lo[3]);
                        result.Rows.Add(row);
                    }
                }
                else
                {
                    "".Substring(2);
                }
                return result;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Program.Main.Test.GetAllOrder", e.ToString());
                return null;
            }
        }


        public List<Order> GetAllOrder()
        {
            try
            {
                DB.SetQuery("SELECT OrderID, CustomerID, Date, Address, Total, Shipcost, Transfered, CheckOutType FROM [Order]");
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                List<Order> order = new List<Order>();
                Order o = new Order();
                if (llo.Count > 0)
                {
                    foreach (List<object> lo in llo)
                    {
                        o = new Order();
                        o.OrderID = Convert.ToInt16(lo[0]);
                        o.Customer = (new CustomerDAL()).GetCustomerById(Convert.ToInt16(lo[1]));
                        o.Date = Convert.ToDateTime(lo[2]);
                        o.Address = Convert.ToString(lo[3]);
                        o.Total = Convert.ToDouble(lo[4]);
                        o.ShipCost = Convert.ToDouble(lo[5]);
                        o.Transfered = Convert.ToBoolean(lo[6]);
                        o.CheckOutType = Convert.ToInt16(lo[7]);
                        order.Add(o);
                    }
                }
                else
                {
                    "".Substring(2);
                }
                return order;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Program.Main.Test.GetAllOrder", e.ToString());
                return null;
            }
        }
    }
}